package com.yusheng.dao;

import com.yusheng.entity.*;
import com.yusheng.util.JdbcUtil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

public class QueryStaff {
   public static JdbcUtil jdbcUtil=new JdbcUtil();

    public static void main(String[] args) {
//        Stuff stuff= QueryStaff.queryStuffByNo("120001");
//        System.out.println(stuff.getStaffname());
/*        ProcessControlList processControlList=queryProcessControl("项目申请");
        for (ProcessControl processControl:processControlList.list){
            System.out.println(processControl.getProcessNo());
        }
        经测试 queryProcessControl执行成功
 */
        /*------------测试getApprovalRecordsByStuffNo----------      */
       /* ApprovalRecordList approvalRecordList=QueryStaff.getApprovalRecordsByStuffNo("120001");
        for (ApprovalRecord approvalRecord :approvalRecordList.approvalRecordList){
            System.out.println(approvalRecord.getRecordNo());
        }*/
    }

   public static Stuff queryStuffByNo(String account) {

       Stuff stuff=null;
       try {
           jdbcUtil.getConnection();

           jdbcUtil.connection.setAutoCommit(false);
           String sql="SELECT s.staffno,s.staffname,s.staffpwd,s.phone,s.detail,d.departname,\n" +
                   "st.stationname\n" +
                   " FROM staff s\n" +
                   " join  department d\n" +
                   " on s.departmentno=d.departno\n" +
                   " join station st\n" +
                   " on  s.stationno=st.stationno\n" +
                   " where s.staffno=?";
           jdbcUtil.preparedStatement=jdbcUtil.connection.prepareStatement(sql);
           jdbcUtil.preparedStatement.setString(1,account);
           ResultSet resultSet=jdbcUtil.preparedStatement.executeQuery();
           while (resultSet.next()){
               stuff=new Stuff(resultSet.getString(1),resultSet.getString(2),resultSet.getString(3),resultSet.getString(4),resultSet.getString(5),
                       resultSet.getString(6),resultSet.getString(7));
           }
       } catch (SQLException throwables) {
           throwables.printStackTrace();
       }finally {
           jdbcUtil.close();
       }
       //System.out.println(stuff.getStaffname());
      return stuff;


   }


    public static ProcessControlList queryProcessControl(String approvalName) {
        ProcessControlList processControlList=new ProcessControlList();
        //连接数据库还是那几个步骤
        //1.注册数据库驱动Driver
        //2.注册之后   ----》获取数据库连接对象（也就是建立通道）-----》获取数据库操作对象---》为sql参数赋值----》执行——————》处理resultset
        try {
            jdbcUtil.getConnection();

            String sql="select p.processno,p.processname,p.processnum,p.currentstep,p.approvalno\n" +
                    "from processcontrol p where processname=?";
            jdbcUtil.preparedStatement=jdbcUtil.connection.prepareStatement(sql);
            jdbcUtil.preparedStatement.setString(1,approvalName);
            ResultSet resultSet=jdbcUtil.preparedStatement.executeQuery();
            while (resultSet.next()){
                ProcessControl processControl=new ProcessControl(resultSet.getString(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getString(4),
                        resultSet.getString(5));
                processControlList.list.add(processControl);
                processControlList.count++;
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtil.close();
        }
        return processControlList;
    }

    public static boolean addApprovalRecord(ApprovalRecord approvalRecord, Connection connection) {
        boolean success=false;
        try {
            jdbcUtil.connection1=connection;

            String sql="insert into approvalrecords(staffno,approvalno,mainrecord,currentrecord,finished,time,recordname,recordopinion)\n" +
                    "VALUES(?,?,?,?,?,?,?,?)";
            jdbcUtil.preparedStatement=jdbcUtil.connection1.prepareStatement(sql);
            jdbcUtil.preparedStatement.setString(1,approvalRecord.getStuffNo());
            jdbcUtil.preparedStatement.setString(2,approvalRecord.getApprovalNo());
            jdbcUtil.preparedStatement.setString(3,approvalRecord.getMainRecord());
            jdbcUtil.preparedStatement.setString(4,approvalRecord.getCurrentRecord());
            jdbcUtil.preparedStatement.setString(5,approvalRecord.getFinished());
            jdbcUtil.preparedStatement.setString(6,approvalRecord.getTime());
            jdbcUtil.preparedStatement.setString(7,approvalRecord.getRecordName());
            jdbcUtil.preparedStatement.setString(8,approvalRecord.getRecordOpinin());
            int num=jdbcUtil.preparedStatement.executeUpdate();
            if (num>0) success=true;

        } catch (SQLException throwables) {
            try {
                jdbcUtil.connection1.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throwables.printStackTrace();

        }
        return success;

    }

    public static void setMainCode(String mainRecord) {
        try {
            jdbcUtil.getConnection();

            String sql="select recordno from approvalrecords where mainrecord=? limit 1";
            jdbcUtil.preparedStatement=jdbcUtil.connection.prepareStatement(sql);
            jdbcUtil.preparedStatement.setString(1,mainRecord);
            ResultSet resultSet=jdbcUtil.preparedStatement.executeQuery();
            resultSet.next();
            String mainRecord1=resultSet.getString(1);
            sql="update approvalrecords set mainrecord=?  where mainrecord=?";
            jdbcUtil.preparedStatement=jdbcUtil.connection.prepareStatement(sql);
            jdbcUtil.preparedStatement.setString(1,mainRecord1);
            jdbcUtil.preparedStatement.setString(2,mainRecord);
            //jdbcUtil.preparedStatement.executeUpdate();

            int num=jdbcUtil.preparedStatement.executeUpdate();


        } catch (SQLException throwables) {

            throwables.printStackTrace();

        }
    }

    /**
     * 根据申请人号，获取多个申请人记录。
     * @param stuffNo
     * @return
     */
    public static ApprovalRecordList getApprovalRecordsByStuffNo(String stuffNo) {
        ApprovalRecordList approvalRecordList=new ApprovalRecordList();
        try {
            jdbcUtil.getConnection();

            String sql="select a.recordno,a.staffno,b.staffname,a.approvalno,c.staffname,a.mainrecord,a.currentrecord,a.finished,a.time, a.recordname,a.recordopinion,a.recordopinion1\n" +
                    "from approvalrecords a\n" +
                    "join staff b\n" +
                    "on  a.staffno=b.staffno\n" +
                    "join staff c\n" +
                    "on a.approvalno=c.staffno\n" +
                    "where a.recordno in(select  max(recordno)\n" +
                    "from approvalrecords\n" +
                    "group by mainrecord) and a.staffno=?\n" +
                    "ORDER BY a.time desc";
            jdbcUtil.preparedStatement=jdbcUtil.connection.prepareStatement(sql);
            jdbcUtil.preparedStatement.setString(1,stuffNo);
            ResultSet resultSet=jdbcUtil.preparedStatement.executeQuery();
            while (resultSet.next()){
                String finished="未审批";
                if ("1".equals(resultSet.getString(8)))    finished="审批没通过";
                else if("2".equals(resultSet.getString(8))) finished="审批通过";
                ApprovalRecord approvalRecord=new ApprovalRecord(
                        resultSet.getString(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getString(4),
                        resultSet.getString(5),
                        resultSet.getString(6),
                        resultSet.getString(7),
                        finished,
                        resultSet.getString(9),
                        resultSet.getString(10),
                        resultSet.getString(11),
                        resultSet.getString(12)
                );
                approvalRecordList.approvalRecordList.add(approvalRecord);
                approvalRecordList.num++;

            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtil.close();
        }
        return approvalRecordList;
    }
}


